import pymysql
from todo import Todo
class TodoManagerWithMysql:
    @staticmethod
    def get_connection():
        return pymysql.Connection(host="192.168.40.134",
                                  database="todos",
                                  user="root",
                                  password="root@123ABC",
                                  autocommit=True)

    def init_db(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
            create table  if not exists todo(
            id int auto_increment primary key,
            title VARCHAR(30) ,
            completed int default 0
            )
            ''')
    def create(self,title):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            sql='insert into todo (title) values(%s)'
            cursor.execute(sql,(title,))
            cursor.execute('select last_insert_id()')
            (id,)=cursor.fetchone()#以元组的形式返回，默认返回第一条记录
            # print((id,))
            return Todo(title,id=id)

    # 把一条记录转为一个object对象
    def covert_row_todo(self, row):
        return Todo(row[1],id=row[0],  completed=True if row[2] == 1 else False)
    def get_list(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from todo")
            todo_list=cursor.fetchall()
            object_list=[]
            for row in todo_list:
                object_list.append(self.covert_row_todo(row))
                #print(todo_list)
                # print(row)
            return object_list



    def get_by_id(self,id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from todo where id=%s",(id,))
            id=cursor.fetchone()
            if id is not None:
                return self.covert_row_todo(id)
            else:
                return None

    def mark_completed(self, id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('update todo set completed=1 where id=%s',(id,))
            return self.get_by_id(id)

    def delete_todo(self,id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('delete from todo where id=%s',(id,))
            return self.get_by_id(id)



# data = TodoManagerWithMysql()
# # data.get_connection()

# data.init_db()
# # data.create("mysql11")
# print(data.get_list())

#print(data.mark_completed(7))